/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jlibrarian;

import jlibrarian.interfaces.CustomerManager;
import jlibrarian.entities.Customer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import javax.sql.DataSource;
import jlibrarian.entities.Rent;
import jlibrarian.interfaces.RentManager;

/**
 *
 * @author dandelion8
 */
public class CustomerManagerImpl implements CustomerManager {

    private DataSource pool;

    public CustomerManagerImpl(DataSource pool) {
        this.pool = pool;
    }

    public synchronized Customer createCustomer(Customer customer) {
        int cid = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = pool.getConnection();
            ps = conn.prepareStatement("INSERT INTO customers (firstName, surname, born, city, street, telephone) VALUES(?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

            ps.setString(1, customer.getFirstName());
            ps.setString(2, customer.getSurname());
            ps.setTimestamp(3, new Timestamp(customer.getBorn().getTime().getTime()));
            ps.setString(4, customer.getCity());
            ps.setString(5, customer.getStreet());
            ps.setString(6, customer.getTelephone());

            ps.execute();

            // gets keyes
            ResultSet keys = ps.getGeneratedKeys();
            keys.next();
            cid = keys.getInt(1);
        } catch (SQLException e) {
            throw new JLibException(e);
        } finally {
            // close
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new JLibException(e);
            }
        }

        // sets keys
        customer.setCid(cid);

        return customer;
    }

    public synchronized boolean deleteCustomer(Customer customer) {
        if (customer.getCid() == 0) {
            throw new IllegalArgumentException("Customer ID can't be null.");
        }


        int rows = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // creating connection
            conn = this.pool.getConnection();
            
            //delete dependent rents from DB
            this.deleteDependentRents(customer);
            
            ps = conn.prepareStatement("DELETE FROM customers WHERE cid = ?");
            ps.setInt(1, customer.getCid());
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            throw new JLibException(e);
        } finally {
            // close
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new JLibException(e);
            }
        }

        return (rows != 0);
    }

    public synchronized boolean updateCustomer(Customer customer) {
        if (customer.getCid() == 0) {
            throw new IllegalArgumentException("Customer ID can't be null.");
        }

        // updated rows
        int rows = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {

            conn = pool.getConnection();
            ps = conn.prepareStatement("UPDATE customers SET firstName = ?, surname = ?, born = ?, city = ?, street = ?, telephone = ? WHERE cid = ?");

            ps.setString(1, customer.getFirstName());
            ps.setString(2, customer.getSurname());
            ps.setTimestamp(3, new Timestamp(customer.getBorn().getTime().getTime()));
            ps.setString(4, customer.getCity());
            ps.setString(5, customer.getStreet());
            ps.setString(6, customer.getTelephone());
            ps.setInt(7, customer.getCid());
            rows = ps.executeUpdate();
        } catch (SQLException e) {
            throw new JLibException(e);
        } finally {
            // close
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new JLibException(e);
            }
        }



        return (rows != 0);
    }

    public synchronized Customer findCustomerById(int id) {
        Customer customer = null;

        // updated rows
        int rows = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {

            conn = pool.getConnection();
            ps = conn.prepareStatement("SELECT * FROM customers WHERE cid = ?");
            ps.setInt(1, id);

            // select the first result
            ResultSet result = ps.executeQuery();
            boolean isNext = result.next();

            // found
            if (isNext) {
                customer = new Customer();
                customer.setCid(result.getInt("cid"));
                customer.setFirstName(result.getString("firstName"));
                customer.setSurname(result.getString("surname"));

                Calendar born = Calendar.getInstance();
                born.setTimeInMillis(result.getTimestamp("born").getTime());

                customer.setBorn(born);
                customer.setTelephone(result.getString("telephone"));
                customer.setCity(result.getString("city"));
                customer.setStreet(result.getString("street"));
            }
        } catch (SQLException e) {
            throw new JLibException(e);
        } finally {
            // close
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new JLibException(e);
            }
        }
        return customer;
    }

    public synchronized Collection<Customer> findCustomerByNames(String firstName, String surname) {
        Collection<Customer> customers = new ArrayList<Customer>();

        // updated rows
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet result;

        if (firstName != null) {
            firstName = "%" + firstName + "%";
        }
        if (surname != null) {
            surname = "%" + surname + "%";
        }

        try {

            conn = pool.getConnection();

            // where to search
            if (firstName != null && surname != null) {
                ps = conn.prepareStatement("SELECT * FROM customers WHERE UPPER(firstName) LIKE UPPER(?) AND UPPER(surname) LIKE UPPER(?)");
                ps.setString(1, firstName);
                ps.setString(2, surname);
            } else if (firstName != null) {
                ps = conn.prepareStatement("SELECT * FROM customers WHERE UPPER(firstName) LIKE UPPER(?)");
                ps.setString(1, firstName);
            } else if (surname != null) {
                ps = conn.prepareStatement("SELECT * FROM customers WHERE UPPER(surname) LIKE UPPER(?)");
                ps.setString(1, surname);
            } else {
                ps = conn.prepareStatement("SELECT * FROM customers");
            }

            result = ps.executeQuery();

            // select the first result
            while (result.next()) {
                Customer customer = new Customer();
                customer.setCid(result.getInt("cid"));
                customer.setFirstName(result.getString("firstName"));
                customer.setSurname(result.getString("surname"));
                Calendar born = Calendar.getInstance();
                born.setTimeInMillis(result.getTimestamp("born").getTime());
                customer.setBorn(born);
                customer.setTelephone(result.getString("telephone"));
                customer.setCity(result.getString("city"));
                customer.setStreet(result.getString("street"));
                customers.add(customer);
            }

        } catch (SQLException e) {
            throw new JLibException(e);
        } finally {
            // close
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new JLibException(e);
            }
        }
        return customers;
    }

    public Collection<Customer> getAllCustomers() {
        return this.findCustomerByNames(null, null);
    }

    private void deleteDependentRents(Customer customer) {
        RentManager rm = new RentManagerImpl(this.pool);
        Collection<Rent> toDelete = rm.findRentsByCustomer(customer, true);

        for (Rent r : toDelete) {
            rm.deleteRent(r);
        }
    }
}
